do "E:\yungyu\lottery_baby\do\set_environment.do"

clear
set more off

global control1 "i.pers_gender i.houhld_resid_cd pers_income_pre1 pers_asset_pre1 pers_earnings_pre1 pers_labor_pre1 married_pre1"
global control2 "totbaby_pre1"
global control3 "pre*redeem*"

use "$wdata\project_lottery_fertility_b3a6_first_5k.dta", clear

gen anybaby_pre1 = totbaby_pre1 > 0
		
gen prizegrp = .
replace prizegrp = 0 if prize10M < 0.001 // < 10k
replace prizegrp = 1 if inrange(prize10M,0.001,0.005) // 10-50k
replace prizegrp = 2 if inrange(prize10M,0.005,0.05) // 50k-500k
replace prizegrp = 3 if inrange(prize10M,0.05,0.5) // 500k-5M
replace prizegrp = 4 if inrange(prize10M,0.5,5) // 5-50M
replace prizegrp = 5 if prize10M >= 5 // >= 50M

forv k = 1(1)5{
	cap gen prizegrp`k' = 0
	replace prizegrp`k' = 1 if prizegrp == `k'
	
	forv j = 3(-1)2{
		cap gen currentxprizegrp`k'xpre`j' = .
		replace currentxprizegrp`k'xpre`j' = .
		replace currentxprizegrp`k'xpre`j' = 0 if prizegrp`k'==0
		replace currentxprizegrp`k'xpre`j' = 0 if prizegrp`k'==1
		replace currentxprizegrp`k'xpre`j' = 1 if prizegrp`k'==1 & current == 1 & norm == -`j'
	}
	forv j = 0(1)6{
		cap gen currentxprizegrp`k'xpost`j' = .
		replace currentxprizegrp`k'xpost`j' = .
		replace currentxprizegrp`k'xpost`j' = 0 if prizegrp`k'==0
		replace currentxprizegrp`k'xpost`j' = 0 if prizegrp`k'==1
		replace currentxprizegrp`k'xpost`j' = 1 if prizegrp`k'==1 & current == 1 & norm == `j'
	}
}	

replace norm_year = norm_year + 3
gen tot_asset = pers_asset + spouse_asset

cap rm "$table/temp/Tab3.xls"
cap rm "$table/temp/Tab3.txt"

** Basic DDD
reghdfe totbaby currentxprizegrp*xp*, a(norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , replace bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** Basic DDD + age FE
reghdfe totbaby currentxprizegrp*xp*, a(age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** Add year fixed effects 
reghdfe totbaby currentxprizegrp*xp*, a(year age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** Add individual control
reghdfe totbaby currentxprizegrp*xp* $control1, a(year age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** Add individual contro2
reghdfe totbaby currentxprizegrp*xp* $control1 $control2, a(year age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** Add individual contro3
reghdfe totbaby currentxprizegrp*xp* $control1 $control2 $control3, a(year age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** W/o Children
reghdfe totbaby currentxprizegrp*xp* $control1 $control2 $control3 if anybaby_pre1 == 0, a(year age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

** W/ Children
reghdfe totbaby currentxprizegrp*xp* $control1 $control2 $control3 if anybaby_pre1 == 1, a(year age norm#current prizegrp#norm prizegrp#current) cl(houseid)
outreg2 using "$table/temp/Tab3.xls" , append bd(3) sd(3) nocon keep(currentxprizegrp*xp*)

replace norm_year = norm_year - 3

gen n = 1
collapse (count)n (mean)totbaby tot_asset prize10M, by(prizegrp current norm)

keep if norm == -1 | norm == 6

gen post = norm > 0
drop norm

reshape wide totbaby tot_asset, i(current prizegrp prize10M n) j(post)

gen bt = totbaby1 - totbaby0
gen bw = tot_asset0
gen btw = tot_asset1 - tot_asset0

keep current prizegrp prize10M n bt bw btw
reshape wide prize10M bt bw btw n, i(prizegrp) j(current)
gen n = n0+n1

keep prizegrp bt0 bw0 btw0 prize10M1 n 
order prizegrp bt0 bw0 btw0 prize10M1 n

drop if prizegrp == 0

drop bw0
save "$table/temp/Tab3_Baseline.dta", replace

import delimited "$table/temp/Tab3.txt", clear

gen grp = substr(v1,strpos(v1,"grp")+3,1) if strpos(v1,"grp") != 0
replace v1 = substr(v1,1,strpos(v1,"grp")+2) + substr(v1,strpos(v1,"grp")+4,.)

gen type = mod(_n,2)
replace grp = grp[_n-1] if grp == ""
replace v1 = v1[_n-1] if v1 == ""

drop in 94
keep in 4/95
replace grp = "1" in 91/92
replace type = 2 in 91/92

gen row = substr(v1,-1,1)
destring row, replace force
replace row = -row if strpos(v1,"pre") != 0
replace row = _n if row == .

keep if strpos(v1,"post6") != 0

replace row = _n
loc N = _N + 2
set obs `N'
replace row = 0 if row == .
sort row
drop row

replace v1 = "" if type == 1
drop type

forv i = 1(1)8{
	loc j = `i'+1
	replace v`j' = "(`i')" in 1
}


replace v1 = "10-50K" in 3
replace v1 = "50-500K" in 5
replace v1 = "500K-5M" in 7
replace v1 = "5M-50M" in 9
replace v1 = ">50M" in 11

drop grp

replace v2 = "All Sample" in 2
replace v8 = "w/o Child" in 2
replace v9 = "w/ Child" in 2

gen prizegrp = .
forv i = 1(1)5{
	loc j = `i'*2 + 1
	replace prizegrp = `i' in `j'
}

gen row = _n
merge m:1 prizegrp using "$table/temp/Tab3_Baseline.dta",nogen
sort row
drop row
drop prizegrp

ap using "$table/temp/FigD3.dta"

drop b2 bw0

gen elasticity = v7 if bt0 != .
replace elasticity = v2 if bt0 != . & v7 == ""
replace elasticity = subinstr(elasticity,"*","",.)
destring elasticity, replace
replace elasticity = (elasticity/bt0)/(prize10M1*10000/btw0)

gen x = n*elasticity
replace elasticity = (x[3]+x[5]+x[7]+x[9]+x[11])/(n[3]+n[5]+n[7]+n[9]+n[11]) in 12 //分組加權平均

replace prize = prize * 10000 //K

tostring bt0 , replace force format(%4.3f)
tostring btw0 , replace force format(%9.0fc)
tostring prize , replace force format(%15.3fc)
tostring n , replace force format(%9.0fc)
tostring elasticity , replace force format(%4.3f)

drop x

replace bt0 = "Baseline Trend" in 1
replace btw0 ="Baseline Trend in Wealth (K)" in 1
replace prize ="Mean Prize (K)" in 1
replace n ="N" in 1
replace elasticity ="Elasticity" in 1

export excel "$table/Tables.xlsx", sheet("Tab3") sheetreplace
